{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "import pandas as pd\n",
    "sys.path.append('..')\n",
    "from configure.settings import DBSelector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "db = DBSelector().mongo('qq')['db_stock']\n",
    "old_doc = db['xueqiu_private']\n",
    "new_doc =db['xueqiu_private_2021-12-28_brute_force']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "only_part_doc =db['xueqiu_private_2021-12-29']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "def mongoToDataframe(doc):\n",
    "    result_list=[]\n",
    "    for i in doc.find({},{'_id':0}):\n",
    "        result_list.append(i)\n",
    "\n",
    "    return pd.DataFrame(result_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "only_part_df = mongoToDataframe(only_part_doc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df = mongoToDataframe(new_doc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "old_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>symbol</th>\n",
       "      <th>name</th>\n",
       "      <th>profit_rate</th>\n",
       "      <th>annual_return_this_year</th>\n",
       "      <th>manager_nick_name</th>\n",
       "      <th>netvalue_date</th>\n",
       "      <th>create_date</th>\n",
       "      <th>close_status</th>\n",
       "      <th>crwaltime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>P000048</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>2021-12-28 22:33:53.371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>P000049</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>2021-12-28 22:33:53.710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>P000051</td>\n",
       "      <td>私募工场(裕泉二期)</td>\n",
       "      <td>-14.18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2015-10-27</td>\n",
       "      <td>2014-04-30</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-12-28 22:33:54.102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>P000057</td>\n",
       "      <td>iMeigu Fund</td>\n",
       "      <td>132.34</td>\n",
       "      <td>-19.24</td>\n",
       "      <td>梁剑</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>2013-10-10</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-12-28 22:33:54.689</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>P000090</td>\n",
       "      <td>私募工场(兴富进取1期)</td>\n",
       "      <td>81.63</td>\n",
       "      <td>NaN</td>\n",
       "      <td>广东兴富投资</td>\n",
       "      <td>2015-11-06</td>\n",
       "      <td>2014-07-08</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-12-28 22:33:55.562</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    symbol          name  profit_rate  annual_return_this_year  \\\n",
       "0  P000048          None          NaN                      NaN   \n",
       "1  P000049          None          NaN                      NaN   \n",
       "2  P000051    私募工场(裕泉二期)       -14.18                      NaN   \n",
       "3  P000057   iMeigu Fund       132.34                   -19.24   \n",
       "4  P000090  私募工场(兴富进取1期)        81.63                      NaN   \n",
       "\n",
       "  manager_nick_name netvalue_date create_date  close_status  \\\n",
       "0              None          None        None         False   \n",
       "1              None          None        None         False   \n",
       "2              None    2015-10-27  2014-04-30          True   \n",
       "3                梁剑    2021-09-30  2013-10-10          True   \n",
       "4            广东兴富投资    2015-11-06  2014-07-08          True   \n",
       "\n",
       "                crwaltime  \n",
       "0 2021-12-28 22:33:53.371  \n",
       "1 2021-12-28 22:33:53.710  \n",
       "2 2021-12-28 22:33:54.102  \n",
       "3 2021-12-28 22:33:54.689  \n",
       "4 2021-12-28 22:33:55.562  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "for index,row in new_df.iterrows():\n",
    "    symbol = row['symbol']\n",
    "    if any(only_part_df['symbol']==symbol):\n",
    "    \n",
    "        max_drawdown_rate=only_part_df[only_part_df['symbol']==symbol]['max_drawdown_rate'].iloc[0]\n",
    "        annual_return_year=only_part_df[only_part_df['symbol']==symbol]['annual_return_year'].iloc[0]\n",
    "        # row['max_drawdown_rate']=max_drawdown_rate\n",
    "        # row['annual_return_year']=annual_return_year\n",
    "        new_df.loc[index,'max_drawdown_rate']=max_drawdown_rate\n",
    "        new_df.loc[index,'annual_return_year']=annual_return_year\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 770 entries, 0 to 769\n",
      "Data columns (total 11 columns):\n",
      " #   Column                   Non-Null Count  Dtype         \n",
      "---  ------                   --------------  -----         \n",
      " 0   symbol                   770 non-null    object        \n",
      " 1   name                     716 non-null    object        \n",
      " 2   profit_rate              677 non-null    float64       \n",
      " 3   annual_return_this_year  529 non-null    float64       \n",
      " 4   manager_nick_name        454 non-null    object        \n",
      " 5   netvalue_date            716 non-null    object        \n",
      " 6   create_date              716 non-null    object        \n",
      " 7   close_status             770 non-null    bool          \n",
      " 8   crwaltime                770 non-null    datetime64[ns]\n",
      " 9   max_drawdown_rate        397 non-null    float64       \n",
      " 10  annual_return_year       397 non-null    float64       \n",
      "dtypes: bool(1), datetime64[ns](1), float64(4), object(5)\n",
      "memory usage: 61.0+ KB\n"
     ]
    }
   ],
   "source": [
    "new_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "def convertor(df):\n",
    "    # df['annual_return_this_year']=df['annual_return_this_year']*100\n",
    "    df['annual_return_year']=df['annual_return_year']*100\n",
    "    df['max_drawdown_rate']=df['max_drawdown_rate']*100\n",
    "    # df['profit_rate']=df['profit_rate']*100\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df = convertor(new_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "process_df = new_df[~new_df['profit_rate'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "process_df = process_df[~process_df['name'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "677"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(process_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>symbol</th>\n",
       "      <th>name</th>\n",
       "      <th>profit_rate</th>\n",
       "      <th>annual_return_this_year</th>\n",
       "      <th>manager_nick_name</th>\n",
       "      <th>netvalue_date</th>\n",
       "      <th>create_date</th>\n",
       "      <th>close_status</th>\n",
       "      <th>crwaltime</th>\n",
       "      <th>max_drawdown_rate</th>\n",
       "      <th>annual_return_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>515</th>\n",
       "      <td>P000777</td>\n",
       "      <td>大禾投资-掘金5号</td>\n",
       "      <td>1217.20</td>\n",
       "      <td>-6.37</td>\n",
       "      <td>大禾投资</td>\n",
       "      <td>2021-12-24</td>\n",
       "      <td>2016-10-12</td>\n",
       "      <td>False</td>\n",
       "      <td>2021-12-28 22:40:03.485</td>\n",
       "      <td>39.31</td>\n",
       "      <td>-0.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>767</th>\n",
       "      <td>P001029</td>\n",
       "      <td>仙人掌盈沣远航一号</td>\n",
       "      <td>1085.60</td>\n",
       "      <td>98.43</td>\n",
       "      <td>上海仙人掌</td>\n",
       "      <td>2021-12-24</td>\n",
       "      <td>2017-04-12</td>\n",
       "      <td>False</td>\n",
       "      <td>2021-12-28 22:42:14.395</td>\n",
       "      <td>27.97</td>\n",
       "      <td>97.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>684</th>\n",
       "      <td>P000946</td>\n",
       "      <td>正圆壹号</td>\n",
       "      <td>984.00</td>\n",
       "      <td>164.31</td>\n",
       "      <td>正圆投资</td>\n",
       "      <td>2021-12-24</td>\n",
       "      <td>2016-07-14</td>\n",
       "      <td>False</td>\n",
       "      <td>2021-12-28 22:41:37.622</td>\n",
       "      <td>36.38</td>\n",
       "      <td>143.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>691</th>\n",
       "      <td>P000953</td>\n",
       "      <td>涌津涌赢1号</td>\n",
       "      <td>854.70</td>\n",
       "      <td>45.40</td>\n",
       "      <td>涌津投资</td>\n",
       "      <td>2021-12-24</td>\n",
       "      <td>2016-04-28</td>\n",
       "      <td>False</td>\n",
       "      <td>2021-12-28 22:41:43.728</td>\n",
       "      <td>25.21</td>\n",
       "      <td>40.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>603</th>\n",
       "      <td>P000865</td>\n",
       "      <td>达理1号</td>\n",
       "      <td>799.52</td>\n",
       "      <td>17.94</td>\n",
       "      <td>成于思</td>\n",
       "      <td>2021-12-24</td>\n",
       "      <td>2016-07-28</td>\n",
       "      <td>False</td>\n",
       "      <td>2021-12-28 22:40:52.152</td>\n",
       "      <td>34.04</td>\n",
       "      <td>22.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>P000736</td>\n",
       "      <td>巨石鑫鼎一号</td>\n",
       "      <td>631.22</td>\n",
       "      <td>175.99</td>\n",
       "      <td>梁雄登</td>\n",
       "      <td>2021-12-24</td>\n",
       "      <td>2020-06-17</td>\n",
       "      <td>False</td>\n",
       "      <td>2021-12-28 22:39:39.807</td>\n",
       "      <td>30.99</td>\n",
       "      <td>184.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>284</th>\n",
       "      <td>P000531</td>\n",
       "      <td>希瓦小牛1号</td>\n",
       "      <td>629.60</td>\n",
       "      <td>1.54</td>\n",
       "      <td>梁宏</td>\n",
       "      <td>2021-12-24</td>\n",
       "      <td>2015-04-21</td>\n",
       "      <td>False</td>\n",
       "      <td>2021-12-28 22:37:01.549</td>\n",
       "      <td>29.79</td>\n",
       "      <td>5.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>690</th>\n",
       "      <td>P000952</td>\n",
       "      <td>龙航一期</td>\n",
       "      <td>611.60</td>\n",
       "      <td>16.29</td>\n",
       "      <td>龙航资产</td>\n",
       "      <td>2021-12-24</td>\n",
       "      <td>2018-12-28</td>\n",
       "      <td>False</td>\n",
       "      <td>2021-12-28 22:41:43.273</td>\n",
       "      <td>26.68</td>\n",
       "      <td>16.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>619</th>\n",
       "      <td>P000881</td>\n",
       "      <td>山楂树一期基金A</td>\n",
       "      <td>526.00</td>\n",
       "      <td>31.40</td>\n",
       "      <td>山楂树甄琢资产</td>\n",
       "      <td>2021-12-24</td>\n",
       "      <td>2014-08-26</td>\n",
       "      <td>False</td>\n",
       "      <td>2021-12-28 22:41:04.147</td>\n",
       "      <td>41.60</td>\n",
       "      <td>33.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>689</th>\n",
       "      <td>P000951</td>\n",
       "      <td>健顺云2号</td>\n",
       "      <td>472.05</td>\n",
       "      <td>24.70</td>\n",
       "      <td>健顺投资</td>\n",
       "      <td>2021-12-10</td>\n",
       "      <td>2015-06-09</td>\n",
       "      <td>False</td>\n",
       "      <td>2021-12-28 22:41:42.820</td>\n",
       "      <td>26.82</td>\n",
       "      <td>40.70</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      symbol       name  profit_rate  annual_return_this_year  \\\n",
       "515  P000777  大禾投资-掘金5号      1217.20                    -6.37   \n",
       "767  P001029  仙人掌盈沣远航一号      1085.60                    98.43   \n",
       "684  P000946       正圆壹号       984.00                   164.31   \n",
       "691  P000953     涌津涌赢1号       854.70                    45.40   \n",
       "603  P000865       达理1号       799.52                    17.94   \n",
       "475  P000736     巨石鑫鼎一号       631.22                   175.99   \n",
       "284  P000531     希瓦小牛1号       629.60                     1.54   \n",
       "690  P000952       龙航一期       611.60                    16.29   \n",
       "619  P000881   山楂树一期基金A       526.00                    31.40   \n",
       "689  P000951      健顺云2号       472.05                    24.70   \n",
       "\n",
       "    manager_nick_name netvalue_date create_date  close_status  \\\n",
       "515              大禾投资    2021-12-24  2016-10-12         False   \n",
       "767             上海仙人掌    2021-12-24  2017-04-12         False   \n",
       "684              正圆投资    2021-12-24  2016-07-14         False   \n",
       "691              涌津投资    2021-12-24  2016-04-28         False   \n",
       "603               成于思    2021-12-24  2016-07-28         False   \n",
       "475               梁雄登    2021-12-24  2020-06-17         False   \n",
       "284                梁宏    2021-12-24  2015-04-21         False   \n",
       "690              龙航资产    2021-12-24  2018-12-28         False   \n",
       "619           山楂树甄琢资产    2021-12-24  2014-08-26         False   \n",
       "689              健顺投资    2021-12-10  2015-06-09         False   \n",
       "\n",
       "                  crwaltime  max_drawdown_rate  annual_return_year  \n",
       "515 2021-12-28 22:40:03.485              39.31               -0.30  \n",
       "767 2021-12-28 22:42:14.395              27.97               97.76  \n",
       "684 2021-12-28 22:41:37.622              36.38              143.14  \n",
       "691 2021-12-28 22:41:43.728              25.21               40.52  \n",
       "603 2021-12-28 22:40:52.152              34.04               22.67  \n",
       "475 2021-12-28 22:39:39.807              30.99              184.67  \n",
       "284 2021-12-28 22:37:01.549              29.79                5.65  \n",
       "690 2021-12-28 22:41:43.273              26.68               16.29  \n",
       "619 2021-12-28 22:41:04.147              41.60               33.33  \n",
       "689 2021-12-28 22:41:42.820              26.82               40.70  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df.sort_values(by='profit_rate',ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>symbol</th>\n",
       "      <th>name</th>\n",
       "      <th>profit_rate</th>\n",
       "      <th>annual_return_this_year</th>\n",
       "      <th>manager_nick_name</th>\n",
       "      <th>netvalue_date</th>\n",
       "      <th>create_date</th>\n",
       "      <th>close_status</th>\n",
       "      <th>crwaltime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>P000048</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>2021-12-28 22:33:53.371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>P000049</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>2021-12-28 22:33:53.710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>P000051</td>\n",
       "      <td>私募工场(裕泉二期)</td>\n",
       "      <td>-14.18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2015-10-27</td>\n",
       "      <td>2014-04-30</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-12-28 22:33:54.102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>P000057</td>\n",
       "      <td>iMeigu Fund</td>\n",
       "      <td>132.34</td>\n",
       "      <td>-19.24</td>\n",
       "      <td>梁剑</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>2013-10-10</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-12-28 22:33:54.689</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>P000090</td>\n",
       "      <td>私募工场(兴富进取1期)</td>\n",
       "      <td>81.63</td>\n",
       "      <td>NaN</td>\n",
       "      <td>广东兴富投资</td>\n",
       "      <td>2015-11-06</td>\n",
       "      <td>2014-07-08</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-12-28 22:33:55.562</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    symbol          name  profit_rate  annual_return_this_year  \\\n",
       "0  P000048          None          NaN                      NaN   \n",
       "1  P000049          None          NaN                      NaN   \n",
       "2  P000051    私募工场(裕泉二期)       -14.18                      NaN   \n",
       "3  P000057   iMeigu Fund       132.34                   -19.24   \n",
       "4  P000090  私募工场(兴富进取1期)        81.63                      NaN   \n",
       "\n",
       "  manager_nick_name netvalue_date create_date  close_status  \\\n",
       "0              None          None        None         False   \n",
       "1              None          None        None         False   \n",
       "2              None    2015-10-27  2014-04-30          True   \n",
       "3                梁剑    2021-09-30  2013-10-10          True   \n",
       "4            广东兴富投资    2015-11-06  2014-07-08          True   \n",
       "\n",
       "                crwaltime  \n",
       "0 2021-12-28 22:33:53.371  \n",
       "1 2021-12-28 22:33:53.710  \n",
       "2 2021-12-28 22:33:54.102  \n",
       "3 2021-12-28 22:33:54.689  \n",
       "4 2021-12-28 22:33:55.562  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "dead_private_fund = new_df[new_df['netvalue_date']<'2021-11-15']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dead_private_fund"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "old_df = convertor(old_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "old_symbol_list = old_df['symbol'].values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "311"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(old_symbol_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_symbol_list = new_df['symbol'].values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "420"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(new_symbol_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "dead_list = []\n",
    "for i in old_symbol_list:\n",
    "    if i not in new_symbol_list:\n",
    "        dead_list.append(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "64"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(dead_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dead_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df.sort_values(by='symbol',ascending=False)['symbol'][:50]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(new_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df=new_df[new_df['name'].notnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "716"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(new_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "78"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(new_df[new_df['close_status']==True]) # 关停"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "495"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(new_df[(new_df['profit_rate']>=0)&(new_df['close_status']==False)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "677"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(process_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "75"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sum(process_df['close_status']==True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "closed=process_df[process_df['close_status']==True]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "process_df[process_df['close_status']==True]['profit_rate']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "process_df.to_excel(\"snoball_private_fund.xlsx\",encoding=\"utf8\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.11078286558345643"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "75/677"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "closed['profit_rate'].argmin()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "symbol                                        P000211\n",
       "name                                               赤升\n",
       "profit_rate                                    -97.49\n",
       "annual_return_this_year                           NaN\n",
       "manager_nick_name                                None\n",
       "netvalue_date                              2018-05-21\n",
       "create_date                                2015-12-02\n",
       "close_status                                     True\n",
       "crwaltime                  2021-12-28 22:34:23.791000\n",
       "max_drawdown_rate                                 NaN\n",
       "annual_return_year                                NaN\n",
       "Name: 15, dtype: object"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "closed.iloc[6]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 677 entries, 2 to 769\n",
      "Data columns (total 11 columns):\n",
      " #   Column                   Non-Null Count  Dtype         \n",
      "---  ------                   --------------  -----         \n",
      " 0   symbol                   677 non-null    object        \n",
      " 1   name                     677 non-null    object        \n",
      " 2   profit_rate              677 non-null    float64       \n",
      " 3   annual_return_this_year  529 non-null    float64       \n",
      " 4   manager_nick_name        448 non-null    object        \n",
      " 5   netvalue_date            677 non-null    object        \n",
      " 6   create_date              677 non-null    object        \n",
      " 7   close_status             677 non-null    bool          \n",
      " 8   crwaltime                677 non-null    datetime64[ns]\n",
      " 9   max_drawdown_rate        397 non-null    float64       \n",
      " 10  annual_return_year       397 non-null    float64       \n",
      "dtypes: bool(1), datetime64[ns](1), float64(4), object(5)\n",
      "memory usage: 58.8+ KB\n"
     ]
    }
   ],
   "source": [
    "process_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "def convert_year(x):\n",
    "    try:\n",
    "        year= (datetime.datetime.strptime(x['netvalue_date'],'%Y-%m-%d') - datetime.datetime.strptime(x['create_date'],'%Y-%m-%d')).days/365\n",
    "    except:\n",
    "        year=None\n",
    "    return year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [],
   "source": [
    "process_df['duration']=process_df.apply(convert_year,axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "449"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "process_df['duration'].argmax()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "symbol                                        P000793\n",
       "name                            中信信托•神农1期证券投资集合资金信托计划\n",
       "profit_rate                                    329.26\n",
       "annual_return_this_year                           NaN\n",
       "manager_nick_name                             陈营长极品投资\n",
       "netvalue_date                              2020-10-30\n",
       "create_date                                2010-07-28\n",
       "close_status                                    False\n",
       "crwaltime                  2021-12-28 22:40:14.451000\n",
       "max_drawdown_rate                                 NaN\n",
       "annual_return_year                                NaN\n",
       "duration                                    10.265753\n",
       "Name: 531, dtype: object"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "process_df.iloc[449]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [],
   "source": [
    "process_df['year_rate'] = process_df['profit_rate']/process_df['duration']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "256"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "process_df['year_rate'].argmin()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>symbol</th>\n",
       "      <th>name</th>\n",
       "      <th>profit_rate</th>\n",
       "      <th>annual_return_this_year</th>\n",
       "      <th>manager_nick_name</th>\n",
       "      <th>netvalue_date</th>\n",
       "      <th>create_date</th>\n",
       "      <th>close_status</th>\n",
       "      <th>crwaltime</th>\n",
       "      <th>max_drawdown_rate</th>\n",
       "      <th>annual_return_year</th>\n",
       "      <th>duration</th>\n",
       "      <th>year_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>316</th>\n",
       "      <td>P000564</td>\n",
       "      <td>彼岸成长</td>\n",
       "      <td>-31.30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2018-12-07</td>\n",
       "      <td>2018-08-23</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-12-28 22:38:16.364</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.290411</td>\n",
       "      <td>-107.778302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189</th>\n",
       "      <td>P000418</td>\n",
       "      <td>泽道复合策略一期</td>\n",
       "      <td>-42.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2018-06-22</td>\n",
       "      <td>2017-09-19</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-12-28 22:36:04.322</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.756164</td>\n",
       "      <td>-56.204710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>249</th>\n",
       "      <td>P000495</td>\n",
       "      <td>南山稳健一号</td>\n",
       "      <td>-33.23</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2018-10-19</td>\n",
       "      <td>2018-01-26</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-12-28 22:36:42.378</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.728767</td>\n",
       "      <td>-45.597556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>394</th>\n",
       "      <td>P000652</td>\n",
       "      <td>测试母基金</td>\n",
       "      <td>-66.33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-11-22</td>\n",
       "      <td>2018-05-02</td>\n",
       "      <td>False</td>\n",
       "      <td>2021-12-28 22:38:59.189</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.558904</td>\n",
       "      <td>-42.549121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>224</th>\n",
       "      <td>P000463</td>\n",
       "      <td>瞭望塔成长</td>\n",
       "      <td>-35.20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2018-10-12</td>\n",
       "      <td>2017-12-04</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-12-28 22:36:25.204</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.854795</td>\n",
       "      <td>-41.179487</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>730</th>\n",
       "      <td>P000992</td>\n",
       "      <td>上海斯诺波嘉源成长</td>\n",
       "      <td>-4.37</td>\n",
       "      <td>-4.37</td>\n",
       "      <td>一手大股东_成嘉源</td>\n",
       "      <td>2021-12-24</td>\n",
       "      <td>2021-11-15</td>\n",
       "      <td>False</td>\n",
       "      <td>2021-12-28 22:41:58.986</td>\n",
       "      <td>5.44</td>\n",
       "      <td>10000.00</td>\n",
       "      <td>0.106849</td>\n",
       "      <td>-40.898718</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>570</th>\n",
       "      <td>P000832</td>\n",
       "      <td>上海斯诺波领军1号A</td>\n",
       "      <td>-32.10</td>\n",
       "      <td>-32.10</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-12-24</td>\n",
       "      <td>2021-03-09</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-12-28 22:40:32.849</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.794521</td>\n",
       "      <td>-40.401724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>701</th>\n",
       "      <td>P000963</td>\n",
       "      <td>上海斯诺波领军1号B</td>\n",
       "      <td>-31.40</td>\n",
       "      <td>-31.40</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-12-24</td>\n",
       "      <td>2021-03-09</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-12-28 22:41:47.407</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.794521</td>\n",
       "      <td>-39.520690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>P000211</td>\n",
       "      <td>赤升</td>\n",
       "      <td>-97.49</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2018-05-21</td>\n",
       "      <td>2015-12-02</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-12-28 22:34:23.791</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.468493</td>\n",
       "      <td>-39.493729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>227</th>\n",
       "      <td>P000467</td>\n",
       "      <td>夏雨</td>\n",
       "      <td>-25.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2018-11-02</td>\n",
       "      <td>2018-02-27</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-12-28 22:36:26.996</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.679452</td>\n",
       "      <td>-37.530242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>655</th>\n",
       "      <td>P000917</td>\n",
       "      <td>博普价值远卓1号1期</td>\n",
       "      <td>-12.17</td>\n",
       "      <td>-12.17</td>\n",
       "      <td>马老师真帅</td>\n",
       "      <td>2021-12-24</td>\n",
       "      <td>2021-08-26</td>\n",
       "      <td>False</td>\n",
       "      <td>2021-12-28 22:41:24.659</td>\n",
       "      <td>16.37</td>\n",
       "      <td>10000.00</td>\n",
       "      <td>0.328767</td>\n",
       "      <td>-37.017083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>P000388</td>\n",
       "      <td>武汉长江一期</td>\n",
       "      <td>-93.40</td>\n",
       "      <td>NaN</td>\n",
       "      <td>用户2109270919</td>\n",
       "      <td>2020-01-10</td>\n",
       "      <td>2017-05-30</td>\n",
       "      <td>False</td>\n",
       "      <td>2021-12-28 22:35:46.686</td>\n",
       "      <td>94.42</td>\n",
       "      <td>-63.13</td>\n",
       "      <td>2.616438</td>\n",
       "      <td>-35.697382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>434</th>\n",
       "      <td>P000692</td>\n",
       "      <td>中泰碧月1月集合资管计划</td>\n",
       "      <td>-40.96</td>\n",
       "      <td>7.68</td>\n",
       "      <td>中泰资管</td>\n",
       "      <td>2021-12-24</td>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>False</td>\n",
       "      <td>2021-12-28 22:39:18.523</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.230137</td>\n",
       "      <td>-33.297105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>P000288</td>\n",
       "      <td>晨星创富</td>\n",
       "      <td>-67.90</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2018-06-22</td>\n",
       "      <td>2016-05-13</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-12-28 22:35:03.749</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.109589</td>\n",
       "      <td>-32.186364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>P000270</td>\n",
       "      <td>清沐众成</td>\n",
       "      <td>-84.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2018-12-21</td>\n",
       "      <td>2016-04-21</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-12-28 22:34:56.258</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.668493</td>\n",
       "      <td>-31.478439</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>342</th>\n",
       "      <td>P000600</td>\n",
       "      <td>万象机遇</td>\n",
       "      <td>-100.00</td>\n",
       "      <td>-100.00</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-12-10</td>\n",
       "      <td>2018-09-27</td>\n",
       "      <td>False</td>\n",
       "      <td>2021-12-28 22:38:33.134</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.205479</td>\n",
       "      <td>-31.196581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>P000316</td>\n",
       "      <td>私募工场(小兵突围一号)</td>\n",
       "      <td>-22.20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2017-04-26</td>\n",
       "      <td>2016-07-11</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-12-28 22:35:15.924</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.791781</td>\n",
       "      <td>-28.038062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>630</th>\n",
       "      <td>P000892</td>\n",
       "      <td>上海斯诺波东盛长安A</td>\n",
       "      <td>-10.40</td>\n",
       "      <td>-10.40</td>\n",
       "      <td>东盛长安海明</td>\n",
       "      <td>2021-12-24</td>\n",
       "      <td>2021-08-09</td>\n",
       "      <td>False</td>\n",
       "      <td>2021-12-28 22:41:12.206</td>\n",
       "      <td>12.33</td>\n",
       "      <td>10000.00</td>\n",
       "      <td>0.375342</td>\n",
       "      <td>-27.708029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>P000293</td>\n",
       "      <td>宫奇锐进</td>\n",
       "      <td>-72.60</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-02-01</td>\n",
       "      <td>2016-05-24</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-12-28 22:35:05.916</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.693151</td>\n",
       "      <td>-26.957274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>237</th>\n",
       "      <td>P000482</td>\n",
       "      <td>银证玄武一期</td>\n",
       "      <td>-88.70</td>\n",
       "      <td>-13.74</td>\n",
       "      <td>玄武道士</td>\n",
       "      <td>2021-03-19</td>\n",
       "      <td>2017-11-17</td>\n",
       "      <td>False</td>\n",
       "      <td>2021-12-28 22:36:36.651</td>\n",
       "      <td>90.25</td>\n",
       "      <td>5.61</td>\n",
       "      <td>3.336986</td>\n",
       "      <td>-26.580870</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      symbol          name  profit_rate  annual_return_this_year  \\\n",
       "316  P000564          彼岸成长       -31.30                      NaN   \n",
       "189  P000418      泽道复合策略一期       -42.50                      NaN   \n",
       "249  P000495        南山稳健一号       -33.23                      NaN   \n",
       "394  P000652         测试母基金       -66.33                      NaN   \n",
       "224  P000463         瞭望塔成长       -35.20                      NaN   \n",
       "730  P000992     上海斯诺波嘉源成长        -4.37                    -4.37   \n",
       "570  P000832    上海斯诺波领军1号A       -32.10                   -32.10   \n",
       "701  P000963    上海斯诺波领军1号B       -31.40                   -31.40   \n",
       "15   P000211            赤升       -97.49                      NaN   \n",
       "227  P000467            夏雨       -25.50                      NaN   \n",
       "655  P000917    博普价值远卓1号1期       -12.17                   -12.17   \n",
       "162  P000388        武汉长江一期       -93.40                      NaN   \n",
       "434  P000692  中泰碧月1月集合资管计划       -40.96                     7.68   \n",
       "72   P000288          晨星创富       -67.90                      NaN   \n",
       "57   P000270          清沐众成       -84.00                      NaN   \n",
       "342  P000600          万象机遇      -100.00                  -100.00   \n",
       "96   P000316  私募工场(小兵突围一号)       -22.20                      NaN   \n",
       "630  P000892    上海斯诺波东盛长安A       -10.40                   -10.40   \n",
       "76   P000293          宫奇锐进       -72.60                      NaN   \n",
       "237  P000482        银证玄武一期       -88.70                   -13.74   \n",
       "\n",
       "    manager_nick_name netvalue_date create_date  close_status  \\\n",
       "316              None    2018-12-07  2018-08-23          True   \n",
       "189              None    2018-06-22  2017-09-19          True   \n",
       "249              None    2018-10-19  2018-01-26          True   \n",
       "394              None    2019-11-22  2018-05-02         False   \n",
       "224              None    2018-10-12  2017-12-04          True   \n",
       "730         一手大股东_成嘉源    2021-12-24  2021-11-15         False   \n",
       "570              None    2021-12-24  2021-03-09          True   \n",
       "701              None    2021-12-24  2021-03-09          True   \n",
       "15               None    2018-05-21  2015-12-02          True   \n",
       "227              None    2018-11-02  2018-02-27          True   \n",
       "655             马老师真帅    2021-12-24  2021-08-26         False   \n",
       "162      用户2109270919    2020-01-10  2017-05-30         False   \n",
       "434              中泰资管    2021-12-24  2020-10-01         False   \n",
       "72               None    2018-06-22  2016-05-13          True   \n",
       "57               None    2018-12-21  2016-04-21          True   \n",
       "342              None    2021-12-10  2018-09-27         False   \n",
       "96               None    2017-04-26  2016-07-11          True   \n",
       "630            东盛长安海明    2021-12-24  2021-08-09         False   \n",
       "76               None    2019-02-01  2016-05-24          True   \n",
       "237              玄武道士    2021-03-19  2017-11-17         False   \n",
       "\n",
       "                  crwaltime  max_drawdown_rate  annual_return_year  duration  \\\n",
       "316 2021-12-28 22:38:16.364                NaN                 NaN  0.290411   \n",
       "189 2021-12-28 22:36:04.322                NaN                 NaN  0.756164   \n",
       "249 2021-12-28 22:36:42.378                NaN                 NaN  0.728767   \n",
       "394 2021-12-28 22:38:59.189                NaN                 NaN  1.558904   \n",
       "224 2021-12-28 22:36:25.204                NaN                 NaN  0.854795   \n",
       "730 2021-12-28 22:41:58.986               5.44            10000.00  0.106849   \n",
       "570 2021-12-28 22:40:32.849                NaN                 NaN  0.794521   \n",
       "701 2021-12-28 22:41:47.407                NaN                 NaN  0.794521   \n",
       "15  2021-12-28 22:34:23.791                NaN                 NaN  2.468493   \n",
       "227 2021-12-28 22:36:26.996                NaN                 NaN  0.679452   \n",
       "655 2021-12-28 22:41:24.659              16.37            10000.00  0.328767   \n",
       "162 2021-12-28 22:35:46.686              94.42              -63.13  2.616438   \n",
       "434 2021-12-28 22:39:18.523                NaN                 NaN  1.230137   \n",
       "72  2021-12-28 22:35:03.749                NaN                 NaN  2.109589   \n",
       "57  2021-12-28 22:34:56.258                NaN                 NaN  2.668493   \n",
       "342 2021-12-28 22:38:33.134                NaN                 NaN  3.205479   \n",
       "96  2021-12-28 22:35:15.924                NaN                 NaN  0.791781   \n",
       "630 2021-12-28 22:41:12.206              12.33            10000.00  0.375342   \n",
       "76  2021-12-28 22:35:05.916                NaN                 NaN  2.693151   \n",
       "237 2021-12-28 22:36:36.651              90.25                5.61  3.336986   \n",
       "\n",
       "      year_rate  \n",
       "316 -107.778302  \n",
       "189  -56.204710  \n",
       "249  -45.597556  \n",
       "394  -42.549121  \n",
       "224  -41.179487  \n",
       "730  -40.898718  \n",
       "570  -40.401724  \n",
       "701  -39.520690  \n",
       "15   -39.493729  \n",
       "227  -37.530242  \n",
       "655  -37.017083  \n",
       "162  -35.697382  \n",
       "434  -33.297105  \n",
       "72   -32.186364  \n",
       "57   -31.478439  \n",
       "342  -31.196581  \n",
       "96   -28.038062  \n",
       "630  -27.708029  \n",
       "76   -26.957274  \n",
       "237  -26.580870  "
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "process_df.sort_values('year_rate',ascending=True).head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "9aa71a1095d6a6dd446f2b486146a60837bd82849c2fc6b4bf786ffe99bd2ba1"
  },
  "kernelspec": {
   "display_name": "Python 3.9.5 64-bit ('3.9': conda)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.5"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
